package linuxbox.util;

import java.sql.*;
import java.text.*;
import org.apache.log4j.Logger;

import linuxbox.BoxStatic;
import linuxbox.bean.*;
import linuxbox.exception.*;

public class DBUtil {
    protected static Logger logger = Logger.getLogger(DBUtil.class.getName());

    public static void setSqlString(Object o, DBField field, StringBuffer sql,
            boolean first) throws Exception {
        setSqlString(o, field, sql, first, " , ");
    }

    public static void setKeySqlString(Object o, DBField field,
            StringBuffer sql, boolean first) throws Exception {
        setSqlString(o, field, sql, first, " and ");
    }

    public static void setSqlString(Object o, DBField field, StringBuffer sql,
            boolean first, String fieldConnector) throws Exception {
        if (field == null && sql == null) {
            logger.error("Error setSqlString, field=" + field + " sql=" + sql);
            throw new LinuxboxException("Error setSqlString, field=" + field
                    + " sql=" + sql);
        }
        if (!first)
            sql.append(fieldConnector);

        sql.append(field.getFieldName());
        sql.append("=");

        appendFieldValue(o, field, sql);
    }

    public static void appendFieldValue(Object o, DBField field,
            StringBuffer sql) throws Exception {
        Object value = null;

        try {
            switch (field.getFieldType()) {
            case DBField.BYTE_TYPE:
                sql.append(field.getField().getInt(o));
                break;

            case DBField.CHAR_TYPE:
                sql.append(field.getField().getChar(o));
                break;

            case DBField.SHORT_TYPE:
                sql.append(field.getField().getShort(o));
                break;

            case DBField.INT_TYPE:
                sql.append(field.getField().getInt(o));
                break;

            case DBField.LONG_TYPE:
                sql.append(field.getField().getLong(o));
                break;

            case DBField.FLOAT_TYPE:
                sql.append(field.getField().getFloat(o));
                break;

            case DBField.DOUBLE_TYPE:
                sql.append(field.getField().getDouble(o));
                break;

            case DBField.STRING_TYPE:
                value = field.getField().get(o);
                appendStringValue(sql, (String) value);
                break;
            case DBField.BOOLEAN_TYPE:
                sql.append(field.getField().getBoolean(o) ? "1" : "0");
                break;

            case DBField.TIMESTAMP_TYPE:
                value = field.getField().get(o);
                if (value == null)
                    sql.append("null");
                else {
                    sql.append("'");
                    sql.append(BoxStatic.formater
                            .DBTimestamp((Timestamp) value));
                    sql.append("'");
                }
                break;

            case DBField.DATE_TYPE:
                value = field.getField().get(o);
                if (value == null)
                    sql.append("null");
                else {
                    sql.append("'");
                    sql.append(BoxStatic.formater.DBDate((Timestamp) value));
                    sql.append("'");
                }
                break;

            case DBField.TIME_TYPE:
                value = field.getField().get(o);
                if (value == null)
                    sql.append("null");
                else {
                    sql.append("'");
                    sql.append(BoxStatic.formater.DBTime((Timestamp) value));
                    sql.append("'");
                }
                break;

            default:
                logger.error("Field type not defined:" + field.getFieldType());
                throw new LinuxboxException("Field type not defined:"
                        + field.getFieldType());
            }
        } catch (Exception e) {
            logger.error("Error setSqlString, field=" + field + " sql=" + sql,
                    e);
            throw new LinuxboxException("Error appendFieldValue, field="
                    + field + " sql=" + sql);
        }
    }

    public static void appendStringValue(StringBuffer sql, String sValue) {
        if (sValue == null)
            sql.append("null ");
        else {
            sql.append("'");
            sql.append(StringUtil.replace(sValue, "\'", "\'\'"));
            sql.append("'");
        }
    }

    public static void setString(StringBuffer sql, String strFieldName,
            String sValue, boolean bFirst) {
        if (strFieldName != null && sql != null) {
            if (!bFirst)
                sql.append(", ");

            sql.append(strFieldName);
            sql.append("=");

            appendStringValue(sql, sValue);
        }
    }

    public static void setInteger(StringBuffer sql, String strFieldName,
            int iValue, boolean bFirst) {
        if (sql != null && strFieldName != null) {
            if (!bFirst)
                sql.append(", ");

            sql.append(strFieldName);
            sql.append("=");
            sql.append(iValue);
        }
    }

    public static void setInteger(StringBuffer sql, String strFieldName,
            long iValue, boolean bFirst) {
        if (sql != null && strFieldName != null) {
            if (!bFirst)
                sql.append(", ");

            sql.append(strFieldName);
            sql.append("=");
            sql.append(iValue);
        }
    }

    public static void setDate(StringBuffer sql, String strFieldName,
            java.sql.Date dtValue, java.sql.Time tmValue, boolean bFirst) {
        if (strFieldName != null && sql != null) {
            if (!bFirst)
                sql.append(", ");

            sql.append(strFieldName);
            sql.append("=");
            if (dtValue == null)
                sql.append("null");
            else {
                // TODO: change to use BoxStatic.formater
                SimpleDateFormat df = new SimpleDateFormat(
                        BoxStatic.POSTGRES_DATE_FORMAT);
                String dtStr = df.format(dtValue);
                df = new SimpleDateFormat(BoxStatic.POSTGRES_TIME_FORMAT);
                String tmStr = df.format(tmValue);
                dtStr = dtStr + " " + tmStr;

                sql.append("'");
                sql.append(dtStr);
                sql.append("'");
            }
        }
    }

    public static void setTimestamp(StringBuffer sql, String strFieldName,
            Timestamp value, boolean bFirst) {
        if (strFieldName != null && sql != null) {
            if (!bFirst)
                sql.append(", ");

            sql.append(strFieldName);
            sql.append("=");
            if (value == null)
                sql.append("null");
            else {
                sql.append("'");
                sql.append(BoxStatic.formater.DBTimestamp(value));
                sql.append("'");
            }
        }
    }

    public static void appendLimit(StringBuffer sql, int from, int limit) {
        // TODO: more Database?
        if (BoxStatic.isPostgreSQL) {
            // PostgresSQL
            if (limit > 0) {
                sql.append(" LIMIT ");
                sql.append(limit);
            }
            if (from >= 0) {
                sql.append(" OFFSET ");
                sql.append(from);
            }
        } else {
            // MySQL
            sql.append(" LIMIT ");

            if (from >= 0) {
                sql.append(from);
                sql.append(",");
            }
            sql.append(limit);
        }
    }
}
